package cn.edu.cjw.APMFP.BOMTable.DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.ResourceBundle;

import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.Connection;
import org.apache.hadoop.hbase.client.Delete;
import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.client.Table;
import org.apache.hadoop.hbase.util.Bytes;

import cn.edu.cjw.APMFP.BOMTable.pojo.BOMTable;
import cn.edu.cjw.APMFP.Util.HBaseUtill;
import cn.edu.cjw.APMFP.Util.HiveUtill;

public class BOMTableDAOImpl implements BOMTableDAO {

	public boolean addAndUpdateBOM(BOMTable bomTable) throws Exception {

		Connection conn = HBaseUtill.getConn();

		Table table = conn.getTable(TableName.valueOf(BOMTable.BOMTABLE_TABLE_NAME));

		Put put = HBaseUtill.putBOM(bomTable);

		table.put(put);

		HBaseUtill.Close(table, conn);

		return true;
	}

	public boolean removeBOM(BOMTable bomTable) throws Exception {

		Connection conn = HBaseUtill.getConn();

		Table table = conn.getTable(TableName.valueOf(BOMTable.BOMTABLE_TABLE_NAME));

		Delete delete = new Delete(Bytes.toBytes(bomTable.getBId()));

		table.delete(delete);

		HBaseUtill.Close(table, conn);

		return true;
	}

	public BOMTable searchById(String id) throws Exception {

		BOMTable bomTable = new BOMTable();

		ResourceBundle bundle = ResourceBundle.getBundle("System");
		String Hive_url = bundle.getString("Hive-url");
		String Hive_user = bundle.getString("Hive-user");
		String Hive_password = bundle.getString("Hive-password");
		java.sql.Connection conn = HiveUtill.getConn(Hive_url, Hive_user, Hive_password);

		String sql = "SELECT * FROM apmfp_bomtable_table WHERE BId=?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, id);

		ResultSet rs = ps.executeQuery();

		while (rs.next()) {

			bomTable.setBId(rs.getString("BId"));
			bomTable.setBName(rs.getString("BName"));
			bomTable.setBtype(rs.getString("Btype"));
			bomTable.setUnit(rs.getString("unit"));

		}

		HiveUtill.Close(rs, ps, conn);

		return bomTable;
	}

	public ArrayList<BOMTable> searchByName(String name) throws Exception {

		ArrayList<BOMTable> list = new ArrayList<BOMTable>();

		ResourceBundle bundle = ResourceBundle.getBundle("System");
		String Hive_url = bundle.getString("Hive-url");
		String Hive_user = bundle.getString("Hive-user");
		String Hive_password = bundle.getString("Hive-password");

		java.sql.Connection conn = HiveUtill.getConn(Hive_url, Hive_user, Hive_password);

		String sql = "SELECT * FROM apmfp_bomtable_table WHERE BName=?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, name);

		ResultSet rs = ps.executeQuery();

		while (rs.next()) {

			BOMTable bomTable = new BOMTable(rs.getString("BId"), rs.getString("BName"), rs.getString("Btype"),
					rs.getString("unit"));
			list.add(bomTable);

		}

		HiveUtill.Close(rs, ps, conn);

		return list;
	}

	public ArrayList<BOMTable> searchByType(String type) throws Exception {

		ArrayList<BOMTable> list = new ArrayList<BOMTable>();

		ResourceBundle bundle = ResourceBundle.getBundle("System");
		String Hive_url = bundle.getString("Hive-url");
		String Hive_user = bundle.getString("Hive-user");
		String Hive_password = bundle.getString("Hive-password");

		java.sql.Connection conn = HiveUtill.getConn(Hive_url, Hive_user, Hive_password);

		String sql = "SELECT * FROM apmfp_bomtable_table WHERE Btype=?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, type);

		ResultSet rs = ps.executeQuery();

		while (rs.next()) {

			BOMTable bomTable = new BOMTable(rs.getString("BId"), rs.getString("BName"), rs.getString("Btype"),
					rs.getString("unit"));
			list.add(bomTable);

		}

		HiveUtill.Close(rs, ps, conn);

		return list;
	}

	public ArrayList<BOMTable> searchByUnit(String unit) throws Exception {
		ArrayList<BOMTable> list = new ArrayList<BOMTable>();

		ResourceBundle bundle = ResourceBundle.getBundle("System");
		String Hive_url = bundle.getString("Hive-url");
		String Hive_user = bundle.getString("Hive-user");
		String Hive_password = bundle.getString("Hive-password");

		java.sql.Connection conn = HiveUtill.getConn(Hive_url, Hive_user, Hive_password);

		String sql = "SELECT * FROM apmfp_bomtable_table WHERE unit=?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1, unit);

		ResultSet rs = ps.executeQuery();

		while (rs.next()) {

			BOMTable bomTable = new BOMTable(rs.getString("BId"), rs.getString("BName"), rs.getString("Btype"),
					rs.getString("unit"));
			list.add(bomTable);

		}

		HiveUtill.Close(rs, ps, conn);

		return list;
	}

	public ArrayList<BOMTable> showAll() throws Exception {
		ArrayList<BOMTable> list = new ArrayList<BOMTable>();

		ResourceBundle bundle = ResourceBundle.getBundle("System");
		String Hive_url = bundle.getString("Hive-url");
		String Hive_user = bundle.getString("Hive-user");
		String Hive_password = bundle.getString("Hive-password");

		java.sql.Connection conn = HiveUtill.getConn(Hive_url, Hive_user, Hive_password);

		String sql = "SELECT * FROM apmfp_bomtable_table";
		Statement st = conn.createStatement();

		ResultSet rs = st.executeQuery(sql);
		while (rs.next()) {

			BOMTable bomTable = new BOMTable(rs.getString("BId"), rs.getString("BName"), rs.getString("Btype"),
					rs.getString("unit"));
			list.add(bomTable);

		}

		HiveUtill.Close(rs, st, conn);

		return list;
	}

}
